package ru.resumeService.service.impl;

import ru.resumeService.model.Resume;
import ru.resumeService.service.ResumeService;
import ru.resumeService.service.db.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ResumeServiceImpl implements ResumeService {

    private static ConnectionFactory factory = ConnectionFactory.getFactory();

    @Override
    public List<Resume> searchResume(String fieldName, String value) {
        List<Resume> resumes = new ArrayList<Resume>();
        Resume resume = null;
        String sql = "SELECT * FROM resume WHERE " + fieldName + " = '" + value + "'";
        ResultSet resultSet;
        try {
            Connection connection = factory.getConnection();
            resultSet = connection.createStatement().executeQuery(sql);
            setResume(resumes,resume,resultSet);
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resumes;
    }

    @Override
    public List<Resume> getResumesByField(String fieldName, String order, int from, int to) {
        List<Resume> resumes = new ArrayList<Resume>();
        Resume resume = null;
        String sql = "SELECT * FROM resume ORDER BY " + fieldName + " " + order + " LIMIT " + from + ", " + (to - from);
        ResultSet resultSet;
        try {
            Connection connection = factory.getConnection();
            resultSet = connection.createStatement().executeQuery(sql);
            setResume(resumes,resume,resultSet);
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resumes;
    }

    @Override
    public List<String> getHeaders() throws SQLException {
        List<String> headers = new ArrayList<String>();
        ResultSetMetaData metaData;
        String sql = "SELECT * FROM resume";
        ResultSet resultSet;
        Connection connection = factory.getConnection();
        resultSet = connection.createStatement().executeQuery(sql);
        int p = 0;
        if (resultSet != null) {
            while (resultSet.next()) {
                p++;
            }
            metaData = resultSet.getMetaData();
            int colCount = metaData.getColumnCount();
            for (int i = 1; i <= colCount; ++i) {
                headers.add(metaData.getColumnName(i));
            }
        }
        connection.close();
        return headers;
    }

    @Override
    public int getCountOfResume() {
        String sql = "SELECT * FROM resume";
        int p = 0;
        ResultSet resultSet;
        try {
            Connection connection = factory.getConnection();
            resultSet = connection.createStatement().executeQuery(sql);
            while (resultSet.next()) {
                p++;
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }

    @Override
    public Resume getResumeById(int id) {
        Resume resume = null;
        String sql = "SELECT * FROM resume WHERE id = " + id + "";
        ResultSet resultSet;
        try {
            Connection connection = factory.getConnection();
            resultSet = connection.createStatement().executeQuery(sql);
            while (resultSet.next()) {
                resume = new Resume();
                resume.setId(resultSet.getInt("id"));
                resume.setName(resultSet.getString("name"));
                resume.setPhone(resultSet.getString("phone"));
                resume.setEmail(resultSet.getString("email"));
                resume.setDate(resultSet.getDate("date"));
                resume.setDepartment(resultSet.getString("department"));
                resume.setCity(resultSet.getString("city"));
                resume.setAge(resultSet.getInt("age"));
                resume.setEducation(resultSet.getString("education"));
                resume.setExperience(resultSet.getInt("experience"));
                resume.setSalary(resultSet.getDouble("salary"));
                resume.setSkills(resultSet.getString("skills"));
                resume.setStatus(resultSet.getInt("status"));
                resume.setComments(resultSet.getString("comment"));
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resume;

    }

    @Override
    public void updateResume(int id, String name, String phone, String email, Date date, String department, String city, int age, String education, int experience, double salary, String skills, int status, String comments) throws SQLException {
        String sql = "UPDATE resume SET name='" + name + "', phone='" + phone + "', email='" + email + "', " +
                "date='" + date + "', department='" + department + "', city='" + city + "', age='" + age + "', " +
                "education='" + education + "', experience='" + experience + "', salary='" + salary + "', " +
                "skills='" + skills + "', status='" + status + "', comment='" + comments + "' WHERE id='" + id + "'";
        Connection connection = factory.getConnection();
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.getConnection().commit();
        statement.getConnection().close();

    }

    @Override
    public void deleteResume(int id) throws SQLException {
        String sql = "DELETE FROM resume WHERE id='" + id + "'";
        Statement statement = factory.getConnection().createStatement();
        statement.executeUpdate(sql);
        statement.getConnection().commit();
        statement.getConnection().close();
    }

    private List<Resume> setResume(List<Resume> resumes,Resume resume,ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            resume = new Resume();
            resume.setId(resultSet.getInt("id"));
            resume.setName(resultSet.getString("name"));
            resume.setPhone(resultSet.getString("phone"));
            resume.setEmail(resultSet.getString("email"));
            resume.setDate(resultSet.getDate("date"));
            resume.setDepartment(resultSet.getString("department"));
            resume.setCity(resultSet.getString("city"));
            resume.setAge(resultSet.getInt("age"));
            resume.setEducation(resultSet.getString("education"));
            resume.setExperience(resultSet.getInt("experience"));
            resume.setSalary(resultSet.getDouble("salary"));
            resume.setSkills(resultSet.getString("skills"));
            resume.setStatus(resultSet.getInt("status"));
            resume.setComments(resultSet.getString("comment"));
            resumes.add(resume);
        }
        return resumes;
    }
}
